package com.example.userdepartment.util;

import com.example.userdepartment.entity.Department;
import com.example.userdepartment.entity.User;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.util.StringUtils;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

/**
 * Excel 工具类
 */
public class ExcelUtil {

    private static final Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

    /**
     * 解析用户Excel文件
     *
     * @param inputStream Excel文件输入流
     * @return 用户列表
     * @throws IOException 如果读取文件出错
     */
    public static List<User> parseUserExcel(InputStream inputStream) throws IOException {
        List<User> users = new ArrayList<>();
        
        try (Workbook workbook = WorkbookFactory.create(inputStream)) {
            Sheet sheet = workbook.getSheetAt(0);
            
            // 跳过标题行和说明行，从第三行开始读取（索引为2）
            int startRow = 2;
            int lastRowNum = sheet.getLastRowNum();
            
            logger.info("开始解析Excel文件，跳过前两行（标题行和说明行），从第3行开始导入，共有 {} 行数据", (lastRowNum + 1));
            logger.info("有效数据行数: {}", (lastRowNum - startRow + 1 > 0 ? lastRowNum - startRow + 1 : 0));
            
            if (lastRowNum < startRow) {
                logger.warn("Excel文件中没有数据行，只有标题行和说明行");
                return users;
            }
            
            for (int i = startRow; i <= lastRowNum; i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    logger.warn("第 {} 行为空，跳过", (i + 1));
                    continue;
                }
                
                User user = new User();
                
                // 读取单元格数据
                String username = getCellValueAsString(row.getCell(0));
                String email = getCellValueAsString(row.getCell(1));
                String realName = getCellValueAsString(row.getCell(2));
                String phone = getCellValueAsString(row.getCell(3));
                String departmentName = getCellValueAsString(row.getCell(4));
                
                // 如果用户名和邮箱都为空，可能是空行，跳过
                if (StringUtils.isEmpty(username) && StringUtils.isEmpty(email)) {
                    logger.warn("第 {} 行用户名和邮箱都为空，可能是空行，跳过", (i + 1));
                    continue;
                }
                
                logger.info("解析第 {} 行数据: username={}, email={}, realName={}, departmentName={}", 
                        (i + 1), username, email, realName, departmentName);
                
                // 设置用户属性
                user.setUsername(username);
                user.setEmail(email);
                user.setRealName(realName);
                user.setPhone(phone);
                user.setDepartmentName(departmentName);
                
                users.add(user);
            }
            
            logger.info("Excel解析完成，共解析 {} 条有效记录", users.size());
        } catch (Exception e) {
            logger.error("解析Excel文件出错", e);
            throw new IOException("解析Excel文件出错: " + e.getMessage(), e);
        }
        
        return users;
    }
    
    /**
     * 导出用户及部门信息到Excel
     *
     * @param userList 用户列表
     * @return Excel文件字节数组
     */
    public static byte[] exportUserData(List<User> userList) throws IOException {
        try (Workbook workbook = new XSSFWorkbook();
             ByteArrayOutputStream out = new ByteArrayOutputStream()) {
            
            // 创建用户信息表
            Sheet userSheet = workbook.createSheet("用户信息");
            
            // 创建标题行样式
            CellStyle headerStyle = workbook.createCellStyle();
            Font headerFont = workbook.createFont();
            headerFont.setBold(true);
            headerStyle.setFont(headerFont);
            headerStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
            headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            headerStyle.setAlignment(HorizontalAlignment.CENTER);
            
            // 创建说明行样式
            CellStyle noteStyle = workbook.createCellStyle();
            Font noteFont = workbook.createFont();
            noteFont.setColor(IndexedColors.RED.getIndex());
            noteStyle.setFont(noteFont);
            
            // 创建表头
            Row headerRow = userSheet.createRow(0);
            String[] headers = {"用户名", "真实姓名", "部门", "邮箱", "手机号", "创建时间"};
            
            for (int i = 0; i < headers.length; i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(headers[i]);
                cell.setCellStyle(headerStyle);
                userSheet.setColumnWidth(i, 4000);
            }
            
            // 添加说明行
            Row noteRow = userSheet.createRow(1);
            Cell noteCell = noteRow.createCell(0);
            noteCell.setCellValue("以下为导出的用户数据，可用于备份或查看");
            noteCell.setCellStyle(noteStyle);
            
            // 合并说明单元格
            userSheet.addMergedRegion(new CellRangeAddress(1, 1, 0, headers.length - 1));
            
            // 填充数据
            int rowNum = 2;
            for (User user : userList) {
                Row row = userSheet.createRow(rowNum++);
                row.createCell(0).setCellValue(user.getUsername() != null ? user.getUsername() : "");
                row.createCell(1).setCellValue(user.getRealName() != null ? user.getRealName() : "");
                
                if (user.getDepartment() != null) {
                    row.createCell(2).setCellValue(user.getDepartment().getName() != null ? user.getDepartment().getName() : "");
                } else if (user.getDepartmentId() != null) {
                    row.createCell(2).setCellValue("部门ID: " + user.getDepartmentId());
                } else {
                    row.createCell(2).setCellValue("未分配");
                }
                
                row.createCell(3).setCellValue(user.getEmail() != null ? user.getEmail() : "");
                row.createCell(4).setCellValue(user.getPhone() != null ? user.getPhone() : "");
                row.createCell(5).setCellValue(user.getCreateTime() != null ? user.getCreateTime().toString() : "");
            }
            
            workbook.write(out);
            return out.toByteArray();
        }
    }
    
    /**
     * 导出部门及用户信息到Excel
     *
     * @param departmentList 部门列表
     * @return Excel文件字节数组
     */
    public static byte[] exportDepartmentData(List<Department> departmentList) throws IOException {
        try (Workbook workbook = new XSSFWorkbook();
             ByteArrayOutputStream out = new ByteArrayOutputStream()) {
            
            // 创建部门信息表
            Sheet deptSheet = workbook.createSheet("部门信息");
            
            // 创建标题行样式
            CellStyle headerStyle = workbook.createCellStyle();
            Font headerFont = workbook.createFont();
            headerFont.setBold(true);
            headerStyle.setFont(headerFont);
            headerStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
            headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            headerStyle.setAlignment(HorizontalAlignment.CENTER);
            
            // 创建说明行样式
            CellStyle noteStyle = workbook.createCellStyle();
            Font noteFont = workbook.createFont();
            noteFont.setColor(IndexedColors.RED.getIndex());
            noteStyle.setFont(noteFont);
            
            // 创建表头
            Row headerRow = deptSheet.createRow(0);
            String[] headers = {"部门名称", "部门描述", "用户数量", "创建时间"};
            
            for (int i = 0; i < headers.length; i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(headers[i]);
                cell.setCellStyle(headerStyle);
                deptSheet.setColumnWidth(i, 5000);
            }
            
            // 添加说明行
            Row noteRow = deptSheet.createRow(1);
            Cell noteCell = noteRow.createCell(0);
            noteCell.setCellValue("以下为导出的部门数据，可用于备份或查看");
            noteCell.setCellStyle(noteStyle);
            
            // 合并说明单元格
            deptSheet.addMergedRegion(new CellRangeAddress(1, 1, 0, headers.length - 1));
            
            // 填充数据
            int rowNum = 2;
            for (Department dept : departmentList) {
                Row row = deptSheet.createRow(rowNum++);
                row.createCell(0).setCellValue(dept.getName() != null ? dept.getName() : "");
                row.createCell(1).setCellValue(dept.getDescription() != null ? dept.getDescription() : "");
                
                int userCount = 0;
                if (dept.getUsers() != null) {
                    userCount = dept.getUsers().size();
                }
                row.createCell(2).setCellValue(userCount);
                row.createCell(3).setCellValue(dept.getCreateTime() != null ? dept.getCreateTime().toString() : "");
            }
            
            workbook.write(out);
            return out.toByteArray();
        }
    }
    
    /**
     * 生成用户导入模板
     *
     * @return Excel工作簿
     * @throws IOException 如果创建文件出错
     */
    public static Workbook generateUserImportTemplate() throws IOException {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("用户导入");
        
        // 创建标题行样式
        CellStyle headerStyle = workbook.createCellStyle();
        Font headerFont = workbook.createFont();
        headerFont.setBold(true);
        headerStyle.setFont(headerFont);
        headerStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        
        // 创建标题行
        Row headerRow = sheet.createRow(0);
        String[] headers = {"用户名*", "邮箱*", "姓名", "电话", "部门名称"};
        
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(headerStyle);
            sheet.setColumnWidth(i, 4000);
        }
        
        // 添加说明行
        Row noteRow = sheet.createRow(1);
        Cell noteCell = noteRow.createCell(0);
        noteCell.setCellValue("带*的字段为必填项，第三行及以下为数据行");
        CellStyle noteStyle = workbook.createCellStyle();
        Font noteFont = workbook.createFont();
        noteFont.setColor(IndexedColors.RED.getIndex());
        noteStyle.setFont(noteFont);
        noteCell.setCellStyle(noteStyle);
        
        // 合并说明单元格
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, headers.length - 1));
        
        // 添加示例数据
        Row exampleRow = sheet.createRow(2);
        exampleRow.createCell(0).setCellValue("zhangsan");
        exampleRow.createCell(1).setCellValue("zhangsan@example.com");
        exampleRow.createCell(2).setCellValue("张三");
        exampleRow.createCell(3).setCellValue("13800138000");
        exampleRow.createCell(4).setCellValue("研发部");
        
        return workbook;
    }

    /**
     * 获取单元格的字符串值
     *
     * @param cell 单元格
     * @return 字符串值，如果单元格为空则返回null
     */
    private static String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return null;
        }
        
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    // 避免科学计数法
                    return String.valueOf((long) cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return null;
        }
    }
} 